﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Web.Configuration;
using System.Text.RegularExpressions;

namespace ForestryEnterprise
{
    public partial class Browse : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                dropBoxFill(0);
                string statement = "Select Tree.* from Tree";
                updateData(statement);
            } 
        }

        private void dropBoxFill(int type)
        {
            this.locArea.Items.Clear();
            this.locName.Items.Clear();

            this.locArea.Items.Add(new ListItem("All"));
            this.locName.Items.Add(new ListItem("All"));

            string statement;
            OleDbConnection connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["forestryDB"].ConnectionString);
            OleDbCommand command;
            OleDbDataReader reader;

            if (type == 0)
            {
                this.locCountry.Items.Clear();
                this.locCountry.Items.Add(new ListItem("All"));

                statement = "select distinct l_country from Location";
                command = new OleDbCommand(statement, connection);
                connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    this.locCountry.Items.Add(new ListItem(reader["l_country"].ToString()));
                }
                reader.Close();
                connection.Close();
            }

            statement = "select distinct l_area from Location";
            command = new OleDbCommand(statement, connection);
            connection.Open();
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                this.locArea.Items.Add(new ListItem(reader["l_area"].ToString()));
            }
            reader.Close();
            connection.Close();

            statement = "select distinct l_name from Location";
            command = new OleDbCommand(statement, connection);
            connection.Open();
            reader = command.ExecuteReader();
            while (reader.Read())
            {
                this.locName.Items.Add(new ListItem(reader["l_name"].ToString()));
            }
            reader.Close();
            connection.Close();
        }

        private void updateData(string statement)
        {
            this.treeData.SelectCommand = statement;
            this.treeDataView.DataBind();
        }

        protected void locCountry_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.locArea.SelectedIndex = 0;
            this.locName.SelectedIndex = 0;

            string statement = "Select distinct Tree.* from Tree";

            if (!this.locCountry.SelectedValue.Equals("All"))
            {
                statement = Regex.Replace(statement, "from Tree", "from (Tree");
                statement += " left join TreeHasLocation on Tree.tree_id = TreeHasLocation.tree_id) ";
                statement += "left join Location on TreeHasLocation.l_id = Location.l_id ";
                statement += "where Location.l_country = '" + this.locCountry.SelectedValue + "'";

                //update areas and names
                this.locArea.Items.Clear();
                this.locName.Items.Clear();

                //areas
                this.locArea.Items.Add(new ListItem("All"));
                string statement2 = "select distinct l_area from Location where l_country = @country";
                OleDbConnection connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["forestryDB"].ConnectionString);
                OleDbCommand command = new OleDbCommand(statement2, connection);
                command.Parameters.AddWithValue("@country", this.locCountry.SelectedValue);
                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    this.locArea.Items.Add(new ListItem(reader["l_area"].ToString()));
                }
                reader.Close();
                connection.Close();

                //names
                this.locName.Items.Add(new ListItem("All"));
                statement2 = "select distinct l_name from Location where l_country = @country";
                connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["forestryDB"].ConnectionString);
                command = new OleDbCommand(statement2, connection);
                command.Parameters.AddWithValue("@country", this.locCountry.SelectedValue);
                connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    this.locName.Items.Add(new ListItem(reader["l_name"].ToString()));
                }
                reader.Close();
                connection.Close();
            }
            else
            {
                dropBoxFill(0);
            }

            updateData(statement);
        }

        protected void locArea_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.locName.SelectedIndex = 0;

            string statement = "Select distinct Tree.* from (Tree";
            statement += " left join TreeHasLocation on Tree.tree_id = TreeHasLocation.tree_id) ";
            statement += "left join Location on TreeHasLocation.l_id = Location.l_id ";
            statement += "where Location.l_country = '" + this.locCountry.SelectedValue + "'";

            this.locName.Items.Clear();
            this.locName.Items.Add(new ListItem("All"));

            if (!this.locArea.SelectedValue.Equals("All"))
            {
                statement += " and Location.l_area = '" + this.locArea.SelectedValue + "'";

                string statement2 = "select distinct l_name from Location where l_country = @country and l_area = @area";
                OleDbConnection connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["forestryDB"].ConnectionString);
                OleDbCommand command = new OleDbCommand(statement2, connection);
                command.Parameters.AddWithValue("@country", this.locCountry.SelectedValue);
                command.Parameters.AddWithValue("@area", this.locArea.SelectedValue);
                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    this.locName.Items.Add(new ListItem(reader["l_name"].ToString()));
                }
                reader.Close();
                connection.Close();
            }
            else
            {
                string statement2 = "select distinct l_name from Location where l_country = @country";
                OleDbConnection connection = new OleDbConnection(WebConfigurationManager.ConnectionStrings["forestryDB"].ConnectionString);
                OleDbCommand command = new OleDbCommand(statement2, connection);
                command.Parameters.AddWithValue("@country", this.locCountry.SelectedValue);

                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    this.locName.Items.Add(new ListItem(reader["l_name"].ToString()));
                }
                reader.Close();
                connection.Close();
            }

            updateData(statement);
        }

        protected void locName_SelectedIndexChanged(object sender, EventArgs e)
        {
            string statement = "Select distinct Tree.* from (Tree";
            statement += " left join TreeHasLocation on Tree.tree_id = TreeHasLocation.tree_id) ";
            statement += "left join Location on TreeHasLocation.l_id = Location.l_id ";
            statement += "where Location.l_country = '" + this.locCountry.SelectedValue + "'";
            statement += " and Location.l_area = '" + this.locArea.SelectedValue + "'";

            if (!this.locName.SelectedValue.Equals("All"))
            {
                statement += " and Location.l_name = '" + this.locName.SelectedValue + "'";
            }

            updateData(statement);
        }
    }
}